# encoding: UTF-8

import mysql.connector

DBUser = "heyu"
DBPassword = "1989512"
DBHost = "10.211.55.7"
DBDatabase = "trade"

class MysqlCursor(object):
    """Mysql上下文"""
    def __init__(self):
        """初始化"""
        self.user = DBUser
        self.password = DBPassword
        self.host = DBHost
        self.db = DBDatabase

    def __enter__(self):
        """上下文进入"""
        self.cnx = mysql.connector.connect(user=self.user, password=self.password, host=self.host, database=self.db)
        self.cur = self.cnx.cursor()
        return self.cur
    def __exit__(self, type, value, trace):
        """上下文退出"""
        #print type, value, trace
        if self.cur:
            self.cur.close()
        if self.cnx:
            self.cnx.commit()
            self.cnx.close()


def CreateVariablesTable():
    """创建变量表"""
    with MysqlCursor() as cur:
        tabSql = """
        CREATE TABLE `trade`.`Variables` (
        `condictionName` VARCHAR(45) NOT NULL,
        `variableName` VARCHAR(45) NOT NULL,
        `startValue` INT NOT NULL,
        `endValue` INT NOT NULL,
        `interval` DECIMAL(8) NOT NULL,
        PRIMARY KEY (`variableName`));
        """
        cur.execute(tabSql)

def CreateStrategyResultTable():
    """创建策略结果表"""
    with MysqlCursor() as cur:
        tabSql = """
        CREATE TABLE `trade`.`StrategyResult` (
        `strategyName` INT NOT NULL,
        `strategyParam` VARCHAR(128) NULL,
        `profit` INT NOT NULL DEFAULT 0,
        `winRation` DECIMAL(4,2) NOT NULL DEFAULT 0,
        `maxDown` DECIMAL(12,2) NOT NULL DEFAULT 0,
        `tradeTimes` INT NOT NULL DEFAULT 0,
        PRIMARY KEY (`strategyParam`));
        """
        cur.execute(tabSql)
def CreateTickCode():
    "创建Tick行情代码表"
    with MysqlCursor() as cur:
        tabSql="""
               CREATE TABLE `trade`.`TickCode` (
               `Instrument` VARCHAR(8) NOT NULL,
               PRIMARY KEY (`Instrument`));
               """
        cur.execute(tabsql)

def CreateCondictionTable():
    """创建条件表"""
    with MysqlCursor() as cur:
        tabSql="""
               CREATE TABLE `trade`.`Condiction` (
               `class` VARCHAR(48) NOT NULL,
               `type` VARCHAR(45) NULL,
               PRIMARY KEY (`class`));
               """
        cur.execute(tabSql)

def loadVariableParam(con):
    """获取变量参数"""
    with MysqlCursor() as cur:
        sql = "select variableName, startValue, endValue, `interval` from Variables where condictionName = \'%s\'" % con
        cur.execute(sql)
        res = cur.fetchall()
        return res

def loadTickCode():
    """获取期货Tick代码"""
    with MysqlCursor() as cur:
        sql = "select * from TickCode"
        cur.execute(sql)
        res = cur.fetchall()
        return res
def loadCondiction(type):
    """获取某种类型的条件
       type = 1     趋势
       type = 2     突破
    """
    with MysqlCursor() as cur:
        sql = "select * from Condiction where type = %d" % type
        cur.execute(sql)
        res = cur.fetchall()
        return res


def storeStrategyResult(result):
    """存储策略结果"""
    with MysqlCursor() as cur:
        sql = """insert into StrategyResult (strategyName, strategyParam, profit, winRation, maxDown, tradeTimes) 
                 values (\"%s\", \"%s\", %d, %f, %f, %d)
              """ % (result['strategyName'], result['strategyParam'], result['profit'], result['winRation'],\
                              result['maxDown'], result['tradeTimes'])
        cur.execute(sql)


if __name__ == '__main__':
    #CreateStrategyResultTable()
    #print loadVariableParam('RSI-PERIOD')
    #result = {'strategyName': 'test', 'strategyParam': {'test': 1,'oo': 2}, 'profit': 0,\
    #                'winRation': 0.5, 'maxDown': -1200, 'tradeTimes': 200}
    #storeStrategyResult(result)
    print loadTickCode()
    print loadCondiction(1)
    print loadCondiction(2)
